* Firm organization with multiple establishments
* Section III.C data preparations 2000-2010 data

clear all 
set matsize 4000
set more off

capture log close
log using log/13_facts-p_dynMEorg_layer-firm_data.log, replace

use untid betnr jahr persnr beruf w93_3_gen ao_kreis hauptbet layer ///
	if jahr >= 2000 & jahr <= 2010 using data/Panel.dta, clear
desc

********************************************************************************
***	Sample restriction *********************************************************
********************************************************************************

*	Only ever multi-establishment firms
egen flg_estjhr = tag(betnr jahr)
bys untid jahr: egen count_est = total(flg_estjhr)
by  untid: egen ever_mbu = max(count_est)
qui keep if ever_mbu > 1
drop ever_mbu

*	Minimum firm size
bys untid jahr: egen empl_unt = count(persnr)
by  untid: egen min_empl = min(empl_unt)
qui keep if min_empl >= 10
drop min_empl

sort betnr jahr layer
egen flg_estjhrlay = tag(betnr jahr layer)
keep if flg_estjhrlay == 1
drop flg_estjhrlay

keep untid jahr betnr count_est empl_unt hauptbet ao_kreis layer
egen max_jahr = max(jahr), by(untid)

********************************************************************************
***	Layer classification based on KldB1988, managerial organization ************
********************************************************************************

tab layer, missing
//	layer based on KldB1988_LayerCMRHFriedrich.dta

bys untid jahr: egen lowest = min(layer)
bys untid jahr: egen second_lowest = min(layer) if layer > lowest
bys untid jahr: egen third_lowest = min(layer) if layer > second_lowest
bys untid jahr: egen highest = min(layer) if layer > third_lowest

gen layer_rank = .
replace layer_rank = 0 if layer == lowest
replace layer_rank = 1 if layer == second_lowest & layer_rank == .
replace layer_rank = 2 if layer == third_lowest & layer_rank == .
replace layer_rank = 3 if layer == highest & layer_rank == .
drop lowest *_lowest highest

//	Number of managerial layers
egen flg_untjhrlay = tag(untid jahr layer_rank)
qui replace flg_untjhrlay = 0 if layer_rank == 0
bys untid jahr: egen count_mgmt_unt = total(flg_untjhrlay)
drop flg_untjhrlay

egen flg_estjhrlay = tag(betnr jahr layer_rank)
qui replace flg_estjhrlay = 0 if layer_rank == 0
bys betnr jahr: egen count_mgmt_bet = total(flg_estjhrlay)
drop flg_estjhrlay

egen flg_estjhr = tag(betnr jahr)
qui keep if flg_estjhr == 1
drop flg_estjhr

//	Maximum number of layers by establishment type
egen max_mgmt_bet = max(count_mgmt_bet), by(untid jahr hauptbet)

********************************************************************************
***	Geography ******************************************************************
********************************************************************************

//	Distance
merge m:1 betnr jahr using data/BHPinclUntID.dta, keepusing(distance_all hq_kreis)
drop if _merge == 2
drop _merge

replace distance_all = 0 if hq_kreis == ao_kreis
cap drop ldistall
gen ldistall = log(distance_all)

bys untid jahr: egen max_dist = max(distance_all)

//	Area
preserve
qui keep if count_est > 2
keep untid jahr betnr ao_kreis
duplicates drop

merge m:1 ao_kreis using data/Middle_coordinates_max-pop.dta
drop if _merge == 2
drop _merge

gen double aux = untid * 10000
gen double untjhr = aux + jahr
drop aux

fieldarea lon lat, id(untjhr) generate(area) unit(sqkm)
tabstat area, s(n mean sd min p10 q p90 max)
// Germany has 360,000 sqkm

save data/MEfirm_area_panel.dta, replace
restore

gen double aux = untid * 10000
gen double untjhr = aux + jahr
drop aux

merge m:1 untjhr using data/MEfirm_area_panel.dta
count if _merge == 3 & count_est == 1
count if _merge != 3 & count_est > 2
drop _merge
drop untjhr

erase data/MEfirm_area_panel.dta

********************************************************************************
***	Firm size ******************************************************************
********************************************************************************

merge m:1 untid jahr using data/Amadeus_Aug2018_untid.dta, keepusing(Sales) 
drop if _merge == 2
drop _merge 

********************************************************************************
***	Reshape ********************************************************************
********************************************************************************

keep untid jahr hauptbet count_est empl_unt Sales count_mgmt_unt max_mgmt_bet area max_dist max_jahr
rename max_mgmt_bet count_mgmt_bet 
duplicates drop

reshape wide count_mgmt_bet, i(untid jahr) j(hauptbet)
xtset, clear
xtset untid jahr

label variable count_mgmt_bet1 "\# mgmt. layers, headquarters"
label variable count_mgmt_bet0 "\# mgmt. layers, establishment"

count

order untid jahr count_est Sales empl_unt area max_dist max_jahr count_mgmt_unt count_mgmt_bet1 count_mgmt_bet0

********************************************************************************
***	Future organizational structure ********************************************
********************************************************************************

/*
Cases in period t: only multi-establishment firms
0-0
0-1,2,3
1-0
1-1
1-2,3
2-0,1
2-2
2-3
3-0,1,2
3-3

Cases in period t+1:
0-0
0-1,2,3
1-0
1-1
1-2,3
2-0,1
2-2
2-3
3-0,1,2
3-3
SE
exit
*/

***	Establishment level
gen f_count_mgmt_bet0 = F.count_mgmt_bet0
order f_count_mgmt_bet0, after(count_mgmt_bet0)
gen f5_count_mgmt_bet0 = F5.count_mgmt_bet0
order f5_count_mgmt_bet0, after(f_count_mgmt_bet0)

gen f_count_mgmt_bet1 = F.count_mgmt_bet1
order f_count_mgmt_bet1, after(count_mgmt_bet1)
gen f5_count_mgmt_bet1 = F5.count_mgmt_bet1
order f5_count_mgmt_bet1, after(f_count_mgmt_bet1)

***	Firm level
tab count_mgmt_unt, m
label variable count_mgmt_unt "\# mgmt. layers, t"
tab count_mgmt_unt if count_est > 1, m

gen f_count_mgmt_unt = F.count_mgmt_unt
label variable f_count_mgmt_unt "\# mgmt. layers, t+1"
gen f_count_mgmt_unt_num = f_count_mgmt_unt
tostring f_count_mgmt_unt, replace
replace f_count_mgmt_unt = "" if f_count_mgmt_unt == "."
replace f_count_mgmt_unt = "SE" if F.count_est == 1
replace f_count_mgmt_unt = "Exit" if f_count_mgmt_unt == "" & jahr < 2010
order f_count_mgmt_unt, after(count_mgmt_unt)

//	Long difference
gen f5_count_mgmt_unt = F5.count_mgmt_unt
label variable f5_count_mgmt_unt "\# mgmt. layers, t+5"
tostring f5_count_mgmt_unt, replace
replace f5_count_mgmt_unt = "" if f5_count_mgmt_unt == "."
replace f5_count_mgmt_unt = "SE" if F5.count_est == 1
tab jahr if f5_count_mgmt_unt != ""
replace f5_count_mgmt_unt = "Exit" if f5_count_mgmt_unt == "" & jahr < 2006
order f5_count_mgmt_unt, after(f_count_mgmt_unt)

gen organization = ""
replace organization = string(count_mgmt_bet1) + "/" + string(count_mgmt_bet0) if count_mgmt_bet1 == count_mgmt_bet0 & count_est > 1
forvalues i=0/3 {
	qui replace organization = "`i'" + "/" + "<`i'" if count_mgmt_bet1 == `i' & count_mgmt_bet0 < `i' & count_est > 1
	qui replace organization = "SubE > HQ" if count_mgmt_bet1 == `i' & count_mgmt_bet0 > `i' & count_est > 1
}
qui replace organization = "SE" if count_est == 1

bys untid (jahr): gen f_organization = organization[_n+1] if jahr == jahr[_n+1]-1
bys untid (jahr): gen f5_organization = organization[_n+5] if jahr == jahr[_n+5]-5

tab organization, m
label variable organization "\# mgmt. layers, est. level, t"

tab f_organization if jahr < 2010, m
tab f_organization if jahr ==2010, m
label variable f_organization "\# mgmt. layers, est. level, t+1"
label variable f5_organization "\# mgmt. layers, est. level, t+5"

count if f_organization == "" & jahr == max_jahr
count if f_organization == "" & jahr == max_jahr & jahr < 2010
//	reflects that for a few firms single years are missing
replace f_organization = "Exit" if f_organization == "" & jahr < 2010
replace f5_organization = "Exit" if f5_organization == "" & jahr < 2006

//	Evolution of hierarchical organization over time
tabstat count_mgmt_unt, by(jahr) c(s) s(N mean sd min p5 q p95 max)

drop if jahr == 2010
tab organization, m

desc
compress 

save data/MEorg_2000-2010_p_layer-firm_transition.dta, replace

********************************************************************************
********************************************************************************

log close
